package bulk_param.dao;

import java.sql.Timestamp;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.swing.JOptionPane;

import bulk_param.modele.OuverturePointModel;


public class InactivationPointDao {
	
	public OuverturePointModel ouverturePointModel;
	private String userid = "arbor";
    private String password = "arbor123";
    private String pilote = "oracle.jdbc.driver.OracleDriver";
    private String url = "jdbc:oracle:thin:@10.1.3.136:1522:cc_cust"; 
    
	private static Connection con;

    //constructor 
	public InactivationPointDao(){
		this.ouverturePointModel= new OuverturePointModel();
	}

	public Connection getConnection(){
				
		try {
			Class.forName(pilote);	

		} catch(java.lang.ClassNotFoundException e) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(e.getMessage());
		}

		try {
			con = DriverManager.getConnection(url, userid, password);
			System.out.println ("Ouverture Connection");     
		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		
		return con;
	}
	
	static  public  void  closeConnection(){         
		System.out.println ("Colse DBConnection.");          
		        
				
		try {
			con.close();             
			System.out.println ("Fermeture Connection");        
			}         
		catch (Exception ex) {             
			System.out.println ("ERROR :"+ex.getMessage());             
			ex.printStackTrace();        
			}    
		} 
	
	
	//Verification de l'existance d'un point
	public boolean isPointExist(String indicatif,String pointZABPQ){
		//Create Connection to the Oracle Database
		getConnection();
		boolean isPointExist =false;
		try{
			System.out.println("Verification du point si il existe");
			String sql="SELECT point FROM USAGE_POINTS "
					+"WHERE point = '"+indicatif+pointZABPQ+"'  "
					+"AND inactive_dt is null";
			
			// Create a prepared statement
 			Statement s = con.createStatement();

			ResultSet rs = s.executeQuery(sql);	
			
			while(rs.next()){ 
				
			if (rs.getString("point") != null){  
				System.out.println("vrai");
				isPointExist= true;	
			}else{
				System.out.println("faux");
				isPointExist= false;	
			}
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		return isPointExist;
	}
	
	public boolean isDateBefore(String indicatif,String pointZABPQ,Date date) throws ParseException{
		//Create Connection to the Oracle Database
		getConnection();
		boolean isDateBefore =false;
		Date dateBase =null;
		Date dateChosen=null;
		String dat2=null;
		try{
			DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
			String dat = dateFormat.format(date);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		
			String sql="SELECT active_dt FROM USAGE_POINTS "
					+"WHERE point = '"+indicatif+pointZABPQ+"'  "
					+"AND inactive_dt is null";
			
			// Create a prepared statement
 			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery(sql);	
			
			while(rs.next()){ 			
				dat2=rs.getString("active_dt");		
			}
			
			System.out.println("ma date parsée : " +dat2);
			dateBase=sdf.parse(dat2);
			dateChosen = sdf.parse(dat);
			
			System.out.println("date base "+sdf.format(dateBase));
			System.out.println("date choisie "+sdf.format(dateChosen));

			if(dateBase.compareTo(dateChosen)>0){
				System.out.println("date inferieur à celui en base");
				isDateBefore= true;
			}else if(dateBase.compareTo(dateChosen)<=0){
				System.out.println("date superieur à celui en base");
				isDateBefore= false;
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		return isDateBefore;
	}
	
	public Timestamp dateInBase(String indicatif,String pointZABPQ) throws ParseException{
		//Create Connection to the Oracle Database
		getConnection();
		Timestamp date =null;
		try{			
			String sql="SELECT active_dt FROM USAGE_POINTS "
					+"WHERE point = '"+indicatif+pointZABPQ+"'  "
					+"AND inactive_dt is null";
			
			// Create a prepared statement
 			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery(sql);	
			
			while(rs.next()){ 			
				date=rs.getTimestamp("active_dt");		
			}
			System.out.println("date en base : "+date);
		}
		catch(Exception e){
			System.out.println(e);
		}
		return date;
	}
		
	//Inactivation Point
	public String inactivePointZABPQ(String pointZABPQ, Date date,Timestamp dateInBase){
		//Create Connection to the Oracle Database
		getConnection();	
		String success="ok";
		try{
			DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd HH:mm:ss");		
			if (date.getDate()==dateInBase.getDate()&& date.getMonth()==dateInBase.getMonth()&& date.getYear()==dateInBase.getYear()){
				System.out.println(" seconde + 1 de la date en base : ");
				date.setSeconds(dateInBase.getSeconds());
				date.setMinutes(dateInBase.getMinutes());
				date.setHours(dateInBase.getHours());
			} else{
				date.setSeconds(0);
				date.setMinutes(0);
				date.setHours(0);
			}				
			String dat = dateFormat.format(date);
			System.out.println("Appel à la procedure stocké INACTIVE_CONTACT_NUM");			
			CallableStatement cStmt = con.prepareCall("{call CPTL_CONFIG_CC.INACTIVATE_CONTACT_NUM(?,?)}");
		    cStmt.setString(1, pointZABPQ);
		    System.out.println("MA DATE : "+dat);
		    cStmt.setString(2, dat);
		    
		    cStmt.executeUpdate();
		    System.out.println("JE SORS MA PROC");			 
			System.out.println("INACTIVATION SUCCESS");	
			
		}
		catch(Exception e){
			System.out.println(e);
			success="ko";
			JOptionPane.showMessageDialog(null, e, "Attention", JOptionPane.WARNING_MESSAGE);
		}
		return success;
	}
}
	
